package com.hgj.dao.impl;

import com.hgj.dao.UserDao;
import com.hgj.entity.User;
import com.hgj.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
    //1.获取连接
    Connection conn=DBUtil.getConnection();
    //2.创建执行sql工具类对象
    QueryRunner qr=new QueryRunner();
    @Override
    public int save(User obj) {
        //3.定义sql语句
        String sql="INSERT INTO  tb_user(user_id,user_name,user_balance,user_point,user_sex,user_birthday,user_phone,user_portrait,user_password) VALUES (?,?,?,?,?,?,?,?,?)";
        //4.传递参数是一个数组 按照占位符顺序传参
        Object[] paras={obj.getUser_id(),
                obj.getUser_name(),
                obj.getUser_balance(),
                obj.getUser_point(),
                obj.getUser_sex(),
                obj.getUser_birthday(),
                obj.getUser_phone(),
                obj.getUser_portrait(),
                obj.getUser_password()
        };
        //5.更新到数据库操作
        //update(连接,sql语句,参数)
        try {
            int row=qr.update(conn,sql,paras);
            return  row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(User obj) {
        //3.定义sql语句
        String sql="UPDATE  tb_user SET  user_name=?,user_balance=?,user_point=?,user_sex=?,user_birthday=?,user_phone=?,user_portrait=?,user_password=? WHERE user_id=? ";
        //4.传递参数是一个数组 按照占位符顺序传参
        Object[] paras={obj.getUser_id(),
                obj.getUser_name(),
                obj.getUser_balance(),
                obj.getUser_point(),
                obj.getUser_sex(),
                obj.getUser_birthday(),
                obj.getUser_phone(),
                obj.getUser_portrait(),
                obj.getUser_password()
        };
        //5.更新到数据库操作
        //update(连接,sql语句,参数)
        try {
            int row=qr.update(conn,sql,paras);
            return  row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Object id) {
        String sql="DELETE FROM tb_user WHERE user_id  = ? ";
        Object[] paras={id};
        try {
            int row=qr.update(conn,sql,paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User findById(Object id) {
        String sql="select * from tb_user " +
                "  where user_id=?";
        Object[] paras={id};
        try {
            //BeanHandler<类型>(类型.class)
            //转换为类型的对象
            User obj=qr.query(conn,sql,paras,
                    new BeanHandler<User>(User.class));
            return  obj;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findAll() {
        String sql="select * from tb_user";
        Object[] paras={}; //参数
        try {
            //转换器
            //BeanListHandler<类型>(类型.class)
            //将查询结果自动转换为当前类型的List集合
            List<User> userList=
                    qr.query(conn,sql,paras,
                            new BeanListHandler<User>(User.class));
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findSearch(String keyword) {
        String sql="select * from tb_user where user_name like concat(concat('%',?),'%')";
        Object[] paras={keyword};
        try {
            List<User> userList=qr.query(conn,sql,paras,new BeanListHandler<User>(User.class));
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findPage(int page, int pagesize) {
        String sql="select * from tb_user " +
                "  limit ?,? ";
        int start=(page-1)*pagesize;
        Object[] paras={start,pagesize};
        try {
            List<User> userList=qr.query(conn,sql,paras,
                    new BeanListHandler<User>(User.class));
            return  userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql="select count(*) from tb_user";
        Object[] paras={};
        try {
            //ScalarHandler<类型>()
            //返回单一数据类型的数值
            //例如Integer Long Float ...
            long nums=qr.query(conn,sql,paras,
                    new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User login(String name) {
        //可以按照用户名 邮箱 电话号码 用户昵称 进行登录
        String sql="select *from tb_user where user_name=? or user_phone=?";
        Object para[] = {name,name};//绑定参数
        try {
            User user = qr.query(conn, sql, para, new BeanHandler<User>(User.class));
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean check(String key, String val) {
        String sql="select *from tb_user where "+key+"=?";
        Object para[] = {val};//绑定参数
        try {
            User user = qr.query(conn, sql, para, new BeanHandler<User>(User.class));
            return null!=user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
